 using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Data;
using NLog;
using DGen;
using Tab2Sql.Classes;
using System.Text.RegularExpressions;

namespace Tab2Sql.Classes.Core
{
 /// <summary>
 /// This holds the actual data.  
 /// </summary>
    public class xTab2Sql : DataTable
    {

        private static Logger logger = LogManager.GetCurrentClassLogger ();
        /// <summary>
        ///
        /// </summary>
        
        private bool isProcessed;

        /// <summary>
        /// Read-only property. Has the Process routine been run.
        /// </summary>
        public bool IsProcessed
        {
            get { return isProcessed; }
        }
        int maximumColumns;




        #region PUBLIC PROPERTIES
        /*---------------------------------------------------------------------------------------------------------------------------------------------
        PUBLIC PROPERTIES 
        ---------------------------------------------------------------------------------------------------------------------------------------------*/
        private DataTable finalTable;
        /// <summary>
        ///
        /// </summary>
        public DataTypes[] ColumnTypes
        {
            get
            {
                return bestGuessColumnTypes.Values.ToList<DataTypes>().ToArray();
            }
        }
        private Dictionary<string, decimal> highest;
		/// <summary>
		/// Holds the highest numeric value for each column. 
		/// </summary>
        public decimal[] Highest
        {
            get { return highest.Values.ToList<decimal>().ToArray(); }
        }
        private Dictionary<string, decimal> lowest;
		/// <summary>
		/// Holds the lowest numeric value for each column. 
		/// </summary>
        public decimal[] Lowest
        {
            get
            {
                decimal[] f = new decimal[ColumnCount];
                int i = 0;
                string cn = "";
                foreach (var foo in lowest)
                {
                    cn = foo.Key;
                    f[i] = lowest[cn];
                    i++;
                }
                return f;
            }
        }
        private Dictionary<string, int> longestLine;
		/// <summary>
		/// Holds the length of the widest column. The term "line" here is a misnomer and is a reference 
		/// to the NOFX song "The Longest Line". This is all about column width. 
		/// </summary>
        public int[] LongestLine
        {
        	get
            {
        		int[] f = new int[ColumnCount];
        		int i = 0;
        		string cn = "";
        		foreach (var foo in longestLine)
                {
        			cn = foo.Key;
        			f[i] = longestLine[cn];
        			i++;
        		}
        		return f;
        	}
        }
		/// <summary>
		/// Numbers of rows detected. 
		/// </summary>
        public int RowCount
        {
        	get { return this.Rows.Count; }
        }
		/// <summary>
		/// Column count in the datatable. 
		/// </summary>
        public int ColumnCount
        {
            get { return this.Columns.Count; }
        }
        // THIS COLUMN COUNT IS LINKED TO THE TEXT NOT THE DATA TABLE
		/// <summary>
		/// Column count in the user input. NOT THE DATATABLE. Check ColumnCount for datatable column count. 
		/// </summary>
        public int InputColumnsCount
        {
        	get
            {
        		int currentCount = 0;
        		int q = 0;
        		for (int i = 0; i < InputLineCount; i++)
                {
        			string[] fields = Lines[i].Split (Delimiter, StringSplitOptions.RemoveEmptyEntries);
        			q = fields.Count ();
        			if (q > currentCount) {
        				currentCount = q;
        			}
        		}
        		return currentCount;
        	}
        }
		/// <summary>
		/// Row count in the user input. NOT THE DATATABLE. Check RowCount for datatable row count. 
		/// </summary>
        public int InputLineCount
        {
            get { return this.Lines.Count(); }
        }
        /// <summary>
        /// Based on string input
        /// </summary>
        /// 
        private string CurrentLine
        {
            get { return this.Lines[CurrentLineNumber]; }
        }
        #endregion
        #region PRIVATE FIELDS
        /*---------------------------------------------------------------------------------------------------------------------------------------------
       Internal Fields / Stuff
       ---------------------------------------------------------------------------------------------------------------------------------------------*/
        private ArrayList allRowsColumnTypes;
        private Options options;
        /// <summary>
        ///
        /// </summary>
        public Options Options
        {
            get { return options; }
        }
        System.Diagnostics.Stopwatch swatch;
        private Dictionary<string, DataTypes> columnTypes;
        private Dictionary<string, DataTypes> bestGuessColumnTypes;
        private string[] Lines;
        private readonly string GlobalSeparator = PrepareText.GlobalDelimiter;
        private int CurrentLineNumber;
        internal string[] Delimiter
        {
            get
            {
                string[] x = new string[1]; 
                x[0] = options.Delimiter; 
                if (x[0] == "\\t") { x[0] = "\t"; }
                if (x[0]== @"\|") {x[0]="|";}

                return x;
            }
        }
        #endregion
        /// <summary>
        ///
        /// </summary>
        public DataTable OurDataTable
        {
            get
            {
                if (finalTable != null && finalTable.Rows.Count > 0)
                {
                    return finalTable;
                }
                else
                {
                    return this;
                }
            }
        }
        /// <summary>
        /// Table name. By default this is #foo
        /// </summary>
        new public readonly string TableName;
        /*---------------------------------------------------------------------------------------------------------------------------------------------
        Constructor 
        ---------------------------------------------------------------------------------------------------------------------------------------------*/
        /// <summary>
        /// 
        /// </summary>
        /// <param name="startText">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="myOptions">
        /// A <see cref="Options"/>
        /// </param>
        public xTab2Sql (string startText, Options myOptions)
        {

            swatch = new System.Diagnostics.Stopwatch ();
            swatch.Start ();
            logger.Trace ("{0}: Constructor called. ", swatch.ElapsedMilliseconds);
			
            isProcessed = false;
            finalTable = new DataTable ();
            options = myOptions;
            TableName = options.TableName;
            maximumColumns = options.MaximumColumns;
            if (TableName.Length < 2)
            {
                TableName = "#foo";
            }
            initStats (startText);
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="startText">
        /// A <see cref="System.String"/>
        /// </param>
        public xTab2Sql (string startText)
        {
            swatch = new System.Diagnostics.Stopwatch ();
            swatch.Start ();
            logger.Trace ("{0}: Constructor called. ", swatch.ElapsedMilliseconds);
            isProcessed = false;
            finalTable = new DataTable ();
            options = new Options ();
            TableName = options.TableName;
            initStats (startText);
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="startText">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="csvMode">
        /// A <see cref="System.Boolean"/>
        /// </param>
        public xTab2Sql (string startText, bool csvMode)
        {
            swatch = new System.Diagnostics.Stopwatch ();
            swatch.Start ();
            logger.Trace ("{0}: Constructor called. ", swatch.ElapsedMilliseconds);
            isProcessed = true;
            CSVtoDataTable csv2dt = new CSVtoDataTable();
            options = new Options();
            finalTable = csv2dt.ParseCSV(startText);
            finalTable.TableName = options.TableName;
            initStats(startText);

        }
        /*---------------------------------------------------------------------------------------------------------------------------------------------
        Public Methods
        ---------------------------------------------------------------------------------------------------------------------------------------------*/
        /// <summary>
        /// Main process loop. This needs to be run before the datatable is properly instantiated. 
        /// </summary>
        public void Process ()
        {
            logger.Trace ("{0}: Process() called. ", swatch.ElapsedMilliseconds);
            DataTypes currentDType = new DataTypes ();
            columnTypes = new Dictionary<string, DataTypes> ();
            int ColCount = InputColumnsCount;
            if (options.FirstRowIsHeader)
            {
                createColumns (ColCount, Lines[0].ToString ());
            }
            else
            {
                createColumns (ColCount);
            }
            this.BeginLoadData ();
            while (DoNext ())
            {
                if (CurrentLineNumber != 0)
                {
                    if (CurrentLineNumber % 200 == 0)
                    {
                        logger.Trace ("{0}: Process loop. Line number {1}.", swatch.ElapsedMilliseconds, CurrentLineNumber);

                    }

        			if (Lines[CurrentLineNumber].Trim ().Length == 0)
                    {
        				// Skipping row.
        			}
                    else
                    {
        				DataRow dr = base.NewRow ();
        				string[] fields = getFields (Lines[CurrentLineNumber], Delimiter, ColCount);
        				int iq = 0;
        				//columnName number
        				foreach (DataColumn dc in base.Columns)
                        {
        					if (iq < Math.Min (fields.Count (), maximumColumns))
                            {
        						string colValue = fields[iq].Trim ();

                                currentDType = DTypes.GuessMyType (colValue);
        						if (currentDType == DataTypes.numericScientific)
                                {
        							colValue = DGen.DText.ConvertScientificNotationToDouble (colValue);
        							currentDType = DataTypes.numeric;
        						}
        						if (currentDType == DataTypes.integer || currentDType == DataTypes.numeric || currentDType == DataTypes.bigint || currentDType == DataTypes.percentage)
                                {
        							if (colValue.Contains ("%"))
                                    {
        								colValue = colValue.Replace ("%", "");
        								if (!colValue.Contains ("."))
                                        {
        									colValue = "0." + colValue;
        								}
        							}
        							if (colValue.Contains ("(")) {
        								colValue = colValue.Replace ("(", "");
        							}
        							if (colValue.Contains (")")) {
        								colValue = colValue.Replace (")", "");
        							}
        						}
        						if (!bestGuessColumnTypes.ContainsKey (dc.ColumnName))
                                {
        							bestGuessColumnTypes.Add (dc.ColumnName, currentDType);
        						}
        						if (!columnTypes.ContainsKey (dc.ColumnName))
                                {
        							columnTypes.Add (dc.ColumnName, currentDType);
        						}
        						colValue = DGen.DText.CleanValue (DTypes.translateDTypes (currentDType), colValue);
        						if (colValue.Length == 0)
                                {
        							dr[dc] = DBNull.Value;
        						}
                                else
                                {
        							dr[dc] = colValue;
        						}
        						updateStatistics (currentDType, colValue, dc.ColumnName);

                                iq++;
        					}
        				}
        				allRowsColumnTypes.Add (columnTypes);

                        this.Rows.Add (dr);
        				columnTypes = new Dictionary<string, DataTypes> ();
        			}
        		}
        	}
        	this.AcceptChanges ();

            
            dataTable2Final();
            logger.Trace("End of Data2Table2Final Stuff");
            isProcessed = true;
        }

        private string[] getFields(string lineOfText, string[] Delimiter, int currentColumnCount)
        {
            var colArray = new ArrayList();
            string[] fields;
            int colCount = 0;
            string field = "";
            string temporaryLine = Lines[CurrentLineNumber];
            if (Delimiter[0] == ",")
            {
                // declare the Regular Expression that will match versus the input string
                var re = new Regex("((?<field>[^\",\\r\\n]+)|\"(?<field>([^\"]|\"\")+)\")(,|$)");
                MatchCollection mc = re.Matches(lineOfText);
                foreach (Match m in mc)
                {
                    // retrieve the field and replace two double-quotes with a single double-quote
                    field = m.Result("${field}").Replace("\"\"", "\"");
                    colArray.Add(field);
                    colCount++;
                }
                if (colCount < currentColumnCount)
                {
                    for (int i = colCount; i < currentColumnCount; i++)
                    {
                        colArray.Add("");
                    }
                }
                fields = colArray.ToArray(typeof(string)) as string[];
            }
            else
            {
                fields = temporaryLine.Split(Delimiter, StringSplitOptions.None);
            }

            return fields;
        }
        /*=============================================================================================================================================
        Private Methods
        ==============================================================================================================================================*/
        /// <summary>
        /// This method actually does everything that the constructor should.
        /// </summary>
        /// <param name="startText">
        /// A <see cref="System.String"/>
        /// </param>
        private void initStats (string startText)
        {
            bestGuessColumnTypes = new Dictionary<string, DataTypes> ();
            columnTypes = new Dictionary<string, DataTypes> ();
            allRowsColumnTypes = new ArrayList ();
            highest = new Dictionary<string, decimal> ();
            lowest = new Dictionary<string, decimal> ();
            longestLine = new Dictionary<string, int> ();
            CurrentLineNumber = 0;
            Lines = startText.Split ('\n');
            
            logger.Trace ("Constructor for xTab2Sql has fired. startText was {0}", startText.Length);
        }

        /// <summary>
        /// Defines / creates columns in heading. 
        /// </summary>
        /// <param name="ColCount"></param>
        /// <param name="p"></param>
        private void createColumns (int ColCount, string p)
        {
        	string temporaryColumnName = "";
        	string[] colNames = p.Split (Delimiter, StringSplitOptions.None);
        	for (int i = 0; i < colNames.Length; i++)
            {
        		temporaryColumnName = Tab2Sql.Classes.Core.TsvHelper.ColumnNameAdjustment (colNames[i], options.ColumnNameModel);
        		try
                {
        			if (!base.Columns.Contains (temporaryColumnName))
                    {
        				base.Columns.Add (temporaryColumnName);
        			}
                    else
                    {
        				base.Columns.Add (temporaryColumnName + String.Format ("_{0}", i));
        			}
        		}
                catch (Exception ex)
                {
        			logger.Error (ex.Message);
                }

            }
            if (options.AddColumnsAddRequired == false)
            {
                maximumColumns = ColCount;
            }
        }
        /// <summary>
        /// Overloaded version for when columnName names are unknown (i.e. there is no header row in input)
        /// </summary>
        /// <param name="ColCount"></param>
        private void createColumns(int ColCount)
        {
            for (int i = 0; i < ColCount; i++)
            {
                base.Columns.Add("C" + i.ToString());
            }
        }
        private bool DoNext()
        {
            if (InputLineCount > CurrentLineNumber + 1)
            {
                CurrentLineNumber++;
                return true;
            }
            else
            { return false; }
        }
        /// <summary>
        /// Maintains the table level statistics for each columnName - e.g. keeps track of the longest / largest / smallest values in that columnName 
        /// so far and, more importantly the datatype of values in that columnName so far.
        /// </summary>
        /// <param name="currentDType"></param>
        /// <param name="colValue"></param>
        /// <param name="colName"></param>
        private void updateStatistics (DataTypes currentDType, string colValue, string colName)
        {

            if (currentDType == DataTypes.integer || currentDType == DataTypes.numeric)
            {
        		if (colValue.Replace ("-", "").Length < colValue.Length - 1)
                {
        			currentDType = DataTypes.varchar;
        		}
        	}

            if (currentDType == DataTypes.integer || currentDType == DataTypes.numeric || currentDType == DataTypes.bigint || currentDType == DataTypes.percentage)
            {
        		if (colValue.Contains ("%")) {
        			colValue = colValue.Replace ("%", "");
        			if (!colValue.Contains ("."))
                    {
        				colValue = "0." + colValue;
        			}
        		}
        		if (colValue.Contains ("(")) {
        			colValue = colValue.Replace ("(", "");
        		}
        		if (colValue.Contains (")")) {
        			colValue = colValue.Replace (")", "");
        		}
        		if (highest.ContainsKey (colName))
                {
        			
                    try
                    {
        				highest[colName] = Math.Max (highest[colName], Convert.ToDecimal (colValue));
        			}
					catch (FormatException fex)
					{
      
					   logger.Error ("updateStatistics error. Message : {0}",fex.Message);
                        Console.WriteLine (fex.Message);
        				highest[colName] = highest[colName];
        			}
        		}
                else {
        			highest.Add (colName, Convert.ToDecimal (colValue));
        		}
        		if (lowest.ContainsKey (colName))
                {
        			try
                    {
        				lowest[colName] = Math.Min (lowest[colName], Convert.ToDecimal (colValue));
        			}

					catch (FormatException fex)
					{
        				logger.Error ("updateStatistics error. Message : {0}",fex.Message);
                        Console.WriteLine(fex.Message);
                        lowest[colName] = lowest[colName];
                    }

                }
                else { lowest.Add(colName, Convert.ToDecimal(colValue)); }
            }
            else
            {
                if (!highest.ContainsKey(colName)) { highest.Add(colName, 0); }
                if (!lowest.ContainsKey(colName)) { lowest.Add(colName, 0); }
            }
            if (longestLine.ContainsKey(colName))
            {
                longestLine[colName] = Math.Max(colValue.Trim().Length, longestLine[colName]);
            }
            else { longestLine.Add(colName, colValue.Trim().Length); }

            if (bestGuessColumnTypes.ContainsKey(colName))
            {
                bestGuessColumnTypes[colName] = DTypes.chooseDataType(currentDType, bestGuessColumnTypes[colName]);
            }
            else { bestGuessColumnTypes.Add(colName, currentDType); }
        }
        /// <summary>
        /// This transfers the data from the base object into the final table which has type defined columns.
        /// </summary>
        private void dataTable2Final ()
        {
        	ArrayList columnsToBeRemoved = new ArrayList ();
        	
            finalTable = new DataTable (this.TableName);
        	foreach (DataColumn dc in this.Columns)
            {
        		if (bestGuessColumnTypes.ContainsKey (dc.ColumnName))
                {
        			Type sqlType = DTypes.translateSqlType (bestGuessColumnTypes[dc.ColumnName]);
        			if (!finalTable.Columns.Contains (dc.ColumnName))
                    {
        				if ((bestGuessColumnTypes[dc.ColumnName] == DataTypes.varchar || bestGuessColumnTypes[dc.ColumnName] == DataTypes.NULL) && longestLine[dc.ColumnName] == 0)
                        {
        					//skip and remove blank columnName.
        					// We cannot do this while enumerating this collection (i.e. the dc.Columns) so we simply add to a list and then remove after this loop is complete.
        					string columnName_tmp = dc.ColumnName;
        					columnsToBeRemoved.Add (columnName_tmp);
        				}
                        else
                        {
        					finalTable.Columns.Add (dc.ColumnName, sqlType);
        					finalTable.Columns[dc.ColumnName].AllowDBNull = true;
        				}
        			}
        		}
        	}
        	foreach (object c2 in columnsToBeRemoved)
            {
        		string c = c2.ToString ();
        		try
                {
        			removeColumn (c);
        		}
                catch (Exception ex)
                {
        			logger.Error ("dataTable2Final error. Column removal bit. Message : {0}", ex.Message);
        		}
        	}
        	finalTable.AcceptChanges ();
        	finalTable.BeginLoadData ();
        	int tempCounter = 0;
        	foreach (DataRow dr in this.Rows)
            {
        		int columnIndex = 0;
        		try
                {
     
        			var newRow = finalTable.NewRow ();
     
        			foreach (var obj in dr.ItemArray)
					{
        				try {
        					newRow[columnIndex] = obj;
        				}
						catch (Exception ex) 
						{
        					logger.Error ("OMG:{0}.ColumnIndex:{1}.object data:{2}", ex.Message, columnIndex, obj.ToString ());
						}
        			}
        			finalTable.ImportRow (newRow);
        		}
                catch (Exception ex) 
				{
        			string quickColumnInfo = "";
        			string finalTableDescription = "";
        			finalTableDescription = DGen.DText.ObjectToString.DataTableSchemaToString (finalTable,":");
					
        			foreach (var dc in dr.ItemArray)
					{
        				quickColumnInfo += dc.ToString ()+":";
						/*
        				string cdtype = dc.DataType.ToString ();
        				string cname = dc.ColumnName.ToString ();
        				string cordinality = dc.Ordinal.ToString ();
        				quickColumnInfo += string.Format ("{0}:{1}:{2}|", cname, cdtype, cordinality);
        				*/
        			}
					quickColumnInfo+="<EOR>";
        			logger.Error ("dataTable2Final error. Loading into final table bit. Row Number {0}. ColumnJunk: {1}: DT Schema : {2}. Message : {3}",tempCounter,quickColumnInfo, finalTableDescription,ex.Message);
					logger.Error(ex.InnerException+"\n"+ex.StackTrace);
						
        		}
				tempCounter++;
            }
            finalTable.AcceptChanges();
            finalTable.EndLoadData();
        }

        private void removeColumn(string columnName_tmp)
        {
            this.Columns.Remove(columnName_tmp);
            bestGuessColumnTypes.Remove(columnName_tmp);
            highest.Remove(columnName_tmp);
            lowest.Remove(columnName_tmp);
            longestLine.Remove(columnName_tmp);
        }

        #region Diagnostic Code
        /// <summary>
        ///
        /// </summary>
        /// <returns>
        /// A <see cref="System.String"/>
        /// </returns>
        public override string ToString()
        {
            string meh = this.Columns[0].ColumnName;
            return DGen.DText.ObjectToString.DataTableToString(this.Select("1=1", meh + " ASC"), "\t");
        }
        private void addSpecialRows(int numberOfSpecialRows)
        {
            if (numberOfSpecialRows == 0) { return; }
            DataRow[] drSquared = getSpecialDataTypeRows(numberOfSpecialRows);
            foreach (DataRow dr3 in drSquared)
            {
                this.Rows.Add(dr3);
            }
            this.AcceptChanges();
        }
        private DataRow[] getSpecialDataTypeRows(int howManyRows)
        {
            howManyRows = Math.Min(this.RowCount, howManyRows);
            DataRow[] dRows = new DataRow[howManyRows];
            DataRow dr;
            for (int i = 0; howManyRows > i; i++)
            {
                dr = base.NewRow();
                if (allRowsColumnTypes.Count >= i)
                {
                    columnTypes = allRowsColumnTypes[i] as Dictionary<string, DataTypes>;
                }
                foreach (var f in columnTypes.Keys)
                {
                    dr[f] = columnTypes[f].ToString();
                }
                dr.AcceptChanges();
                dRows[i] = dr;
            }
            return dRows;
        }
        #endregion
        #region disposal stuff
        /// <summary>
        ///
        /// </summary>
        /// <param name="disposing">
        /// A <see cref="System.Boolean"/>
        /// </param>
        new protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                // dispose managed resources
                this.Clear();
                this.Dispose();
            }
            // free native resources
        }
        /// <summary>
        /// 
        /// </summary>
        new public void Dispose()
        {
			logger.Trace("Disposal / garbage collection called explicitly.");
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion
    }
}
